/**
 * Copyright (C) 2022-2023
 * All rights reserved, Designed By www.tonetn.com
 * 注意：
 * 本软件为www.tonetn.com开发研制，未经购买不得使用
 * 购买后可获得全部源代码（禁止转卖、分享、上传到码云、github等开源平台）
 * 一经发现盗用、分享等行为，将追究法律责任，后果自负
 */
package com.xunyin.modules.shareorder.service.mapper;

import com.xunyin.common.mapper.CoreMapper;
import com.xunyin.modules.shareorder.bo.TeaOrderBo;
import com.xunyin.modules.shareorder.domain.TShareOrder;
import com.xunyin.modules.shareorder.service.dto.ShareChartDataDto;
import com.xunyin.modules.shareorder.service.dto.TShareOrderDto;
import com.xunyin.modules.shareorder.vo.TShareOrderVo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

/**
 * @author admin
 * @date 2022-09-22
 */
@Repository
public interface TShareOrderMapper extends CoreMapper<TShareOrder> {

    /**
     * 获取未分账的订单
     */
    @Select("SELECT * from t_share_order where transaction_id is not null and order_status=4 and fz_flag=0 and fz_result != 'FINISHED'")
    List<TShareOrder> getNotFzOrderList();

    /**
     *
     *
     * 预约时间单内订单预约时间点已使用查询接口
     * @param startTime
     * @param endTime
     * @param teaChildId
     * @return
     */
    @Select("select tea_subscribe_times,tea_server_time,tea_server_end_time from t_share_order where store_room_id = #{teaChildId} " +
            "AND ( " +
            "    (tea_server_start_time >= #{startTime} AND tea_server_end_time <= #{endTime}) " +
            "    OR (tea_server_start_time <= #{startTime} AND #{startTime} <= tea_server_end_time <= #{endTime}) " +
            "    OR ( #{startTime} <= tea_server_start_time <= #{endTime} AND tea_server_end_time >= #{endTime}) " +
            "            ) " +
            "    and status=1 " +
            "    and order_status not in (3,5,6)")
    List<TShareOrder> getTimeListOrder(@Param("startTime") String startTime, @Param("endTime") String endTime, @Param("teaChildId") Integer teaChildId);

    /**
     * 订单总金额
     * @return
     */
    //@Select( "select IFNULL(sum(order_mondey),0)/100  from t_share_order ")
    @Select("select IFNULL(sum(order_mondey),0) from t_share_order ")
    Double sumOrderMoney();

    /**
     * 实付订单总金额
     * @return
     */
/*    @Select("<script> select IFNULL(sum(pay_money),0)/100 from t_share_order " +
            "where is_del=0 and order_status in (1,2,4) " +
            "<if test =\"storeId !=null\"> and store_id = #{storeId} </if> </script>")*/
    @Select("<script> select IFNULL(sum(pay_money),0) from t_share_order " +
            "where is_del=0 and order_status in (1,2,4) " +
            "<if test =\"storeId !=null\"> and store_id = #{storeId} </if> </script>")
    Double sumRealOrderMoney(@Param("storeId") Integer storeId);

    /**
     * 订单总金额
     * @return
     */
    @Select("<script> select IFNULL(sum(order_mondey),0) from t_share_order " +
            "where is_del=0 and order_status in (1,2,4) " +
            "<if test =\"storeId !=null\"> and store_id = #{storeId} </if> </script>")
    Double sumOrderMoney(@Param("storeId") Integer storeId);

    /**
     * 订单总次数
     * @param storeId
     * @return
     */
    @Select("<script> " +
            " select count(*) from t_share_order " +
            " where is_del=0 " +
            " <if test =\"storeId !=null\"> and store_id = #{storeId} </if> " +
            " </script>")
    Integer sumOrderCount(@Param("storeId") Integer storeId);


    /**
     * 包间Id
     * @param storeRoomId
     * @return
     */
    @Select("select count(1) from t_share_order o " +
            "where o.status=1 and o.order_status = 2 and o.store_room_id = #{storeRoomId} ")
    Integer checkStoreRoomOrderInServer(@Param("storeRoomId") Integer storeRoomId);

    /**
     * 根据用户Id查询下单数
     * @param userId
     * @return
     */
    @Select("SELECT count(1) FROM t_share_order " +
            "  where create_user_id = #{userId} " +
            "  and status=1 AND (order_status = 1 OR order_status = 2 OR order_status = 4) ")
    Integer countOrderByCreadUserId(@Param("userId") Integer userId);

    /**
     * 根据设备编号，查看关联的该设备的订单是否存在
     * @param deviceNo
     * @return
     */
    @Select("select count(1) from t_share_order sto " +
            " INNER JOIN t_device sd ON sto.store_room_id = sd.teahouse_id" +
            " where sd.device_no = #{deviceNo} and sto.status = 1 " +
            " AND (sto.order_status = 1 or sto.order_status = 2 or sto.service_status = 6)")
    Integer judgeHasOrder(@Param("deviceNo") String deviceNo);


    @Select("SELECT count(0) from t_share_order o " +
            " LEFT JOIN t_store th ON  o.main_id = th.main_id " +
            " where o.status = 1 and o.parent_order_id = #{parentOrderId} ")
    Integer countTeaHouseOrder(@Param("parentOrderId") Integer parentOrderId);

    /**
     * 查询当前订单Id,相临时间是否续单
     * @param parentOrderId
     * @return
     */
    @Select("select sto.* from t_share_order sto where sto.parent_order_id = #{parentOrderId} order by sto.tea_server_start_time asc ")
    List<TeaOrderBo> orderListByParentOrderId(@Param("parentOrderId") Integer parentOrderId);


    @Select("SELECT * FROM t_share_order " +
            "  where create_user_id = #{userId} " +
            "  and tea_server_end_time > #{date} " +
            "  and status=1 AND (order_status = 1 OR order_status = 2) ORDER BY tea_server_end_time ASC ")
    List<TShareOrderDto> selectOrderByCreadUserId(@Param("userId") Integer userId, @Param("date") Date date);


    @Select("<script> SELECT sto.*,c.icon from t_share_order sto " +
            " left join mt_store sth on sth.id = sto.store_id " +
            " left join mt_store_room c on c.id = sto.store_room_id " +
            " where sto.status = 1  " +
            "<if test =\"userId !=null\"> and sto.create_user_id = #{userId} </if> " +
            //" and sto.order_status = #{orderStatus} " +
            "<if test =\"orderStatus !=null\"> and sto.order_status = #{orderStatus} </if> " +
            "<if test =\"date !=null\"> and sto.tea_server_end_time &gt; now()</if> " +
            " ORDER BY sto.create_time DESC </script>")
    List<TShareOrderVo> orderList(@Param("userId") Integer userId, @Param("orderStatus") Integer orderStatus, @Param("date") Date date);

    @Select("<script> SELECT sto.*,c.icon from t_share_order sto " +
            " left join mt_store sth on sth.id = sto.store_id " +
            " left join mt_store_room c on c.id = sto.store_room_id " +
            " where sto.status = 1  " +
            " <if test =\"storeRoomId !=null\"> and c.id = #{storeRoomId} </if> " +
            " <if test =\"orderStatus !=null\"> and sto.order_status = #{orderStatus} </if> " +
            /*" <if test =\"date !=null\"> and sto.tea_server_end_time &gt; now()</if> " +*/
            " ORDER BY sto.create_time DESC </script>")
    List<TShareOrderVo> orderListByRoomId(@Param("storeRoomId") Integer storeRoomId,
                                             @Param("orderStatus") Integer orderStatus);


    @Select("<script> select sto.*,c.icon,c.store_room_name as storeRoomName,c.id as storeRoomId " +
            " from t_share_order sto " +
            " left join mt_store_room c on c.id = sto.store_room_id " +
            " where sto.store_id = #{storeId} " +
            //" left join mt_store sth on sth.id = sto.store_id " +
            " <if test='dayNum != 365'> " +
            " and (sto.tea_server_end_time >= NOW() - interval #{dayNum} day or sto.tea_server_start_time >= NOW() - interval #{dayNum} day)" +
            " </if> " +
            " <if test =\"orderStatus !=null\"> and sto.order_status = #{orderStatus} </if> " +
            " and sto.status=1 and sto.order_status in (1,2,4) " +
            " ORDER BY sto.tea_server_end_time desc " +
            "</script>")
    List<TShareOrderVo> loadTeaHouseOrder4Manage(@Param("storeId") Integer storeId,
                                                 @Param("orderStatus") Integer orderStatus,
                                                 @Param("dayNum") Integer dayNum);


    @Select("<script> select sto.*,c.icon," +
            "c.store_room_name as storeRoomName," +
            "c.id as storeRoomId," +
            "d.remark as cleanRemark," +
            "d.cleanner_phone as cleannerPhone, " +
            "d.create_time as cleaningTime " +
            " from t_share_order sto " +
            " left join mt_store_room c on c.id = sto.store_room_id " +
            " left join t_cleaning_record d on d.order_id = sto.id " +
            " where sto.store_id = #{storeId} " +
            //" left join mt_store sth on sth.id = sto.store_id " +
            " <if test='dayNum != 365'> " +
            " and (sto.tea_server_end_time >= NOW() - interval #{dayNum} day or sto.tea_server_start_time >= NOW() - interval #{dayNum} day)" +
            " </if> " +
            " <if test =\"orderStatus !=null\"> and sto.order_status = #{orderStatus} </if> " +
            " and sto.status=1 and sto.order_status in (1,2,4) and d.id is not null " +
            " ORDER BY sto.tea_server_end_time desc " +
            "</script>")
    List<TShareOrderVo> loadTeaHouseOrder4ManageByCleaningRecord(@Param("storeId") Integer storeId,
                                                 @Param("orderStatus") Integer orderStatus,
                                                 @Param("dayNum") Integer dayNum);

    /**
     * 实付订单总金额(根据选择天数)
     * @return
     */
    @Select("<script> " +
            " select IFNULL(sum(pay_money),0) from t_share_order " +
            "where is_del=0 and order_status in (1,2,4) " +
            " <if test='dayNum != 365'> " +
            " and (tea_server_end_time >= NOW() - interval #{dayNum} day or tea_server_start_time >= NOW() - interval #{dayNum} day)" +
            " </if> " +
            "<if test =\"storeId !=null\"> and store_id = #{storeId} </if> " +
            "</script>")
    Double sumRealOrderMoneyByDayNum(@Param("storeId") Integer storeId,@Param("dayNum") Integer dayNum);


    /**
     * 订单总金额(根据选择天数)
     * @return
     */
    @Select("<script> " +
            " select IFNULL(sum(order_mondey),0) from t_share_order " +
            "where is_del=0 and order_status in (1,2,4) " +
            " <if test='dayNum != 365'> " +
            " and (tea_server_end_time >= NOW() - interval #{dayNum} day or tea_server_start_time >= NOW() - interval #{dayNum} day)" +
            " </if> " +
            "<if test =\"storeId !=null\"> and store_id = #{storeId} </if> " +
            "</script>")
    Double sumOrderMoneyByDayNum(@Param("storeId") Integer storeId,@Param("dayNum") Integer dayNum);

    /**
     * 订单总次数(根据选择天数)
     * @param storeId
     * @return
     */
    @Select("<script> " +
            " select count(*) from t_share_order " +
            " where is_del=0 and order_status in (1,2,4) " +
            " <if test='dayNum != 365'> " +
            " and (tea_server_end_time >= NOW() - interval #{dayNum} day or tea_server_start_time >= NOW() - interval #{dayNum} day)" +
            " </if> " +
            " <if test =\"storeId !=null\"> and store_id = #{storeId} </if> " +
            " </script>")
    Integer sumOrderCountByDayNum(@Param("storeId") Integer storeId,@Param("dayNum") Integer dayNum);

    @Select("select sto.* from t_share_order sto where sto.status=1 and sto.order_status < 5 and sto.order_status != 3 and sto.service_status < 8")
    List<TeaOrderBo> orderScheduleData();

    /*IFNULL(sum(pay_money),0)/100*/
    @Select("<script>" +
            " SELECT IFNULL(sum(pay_money),0) as num," +
            " DATE_FORMAT(create_time, '%m-%d') as time " +
            " FROM t_share_order where order_status in (1,2,4) and is_del=0 and pay_time >= #{time}" +
            " <if test =\"storeId !=null\"> and store_id = #{storeId} </if> " +
            " GROUP BY DATE_FORMAT(create_time,'%Y-%m-%d') " +
            " ORDER BY create_time ASC" +
            " </script>")
    List<ShareChartDataDto> chartList(@Param("time") Date time, @Param("storeId") Integer storeId);

    @Select("<script> " +
            "SELECT count(id) as num," +
            " DATE_FORMAT(create_time, '%m-%d') as time " +
            " FROM t_share_order where order_status in (1,2,4) and is_del=0 and pay_time >= #{time}" +
            " <if test =\"storeId !=null\"> and store_id = #{storeId} </if> " +
            " GROUP BY DATE_FORMAT(create_time,'%Y-%m-%d') " +
            " ORDER BY create_time ASC " +
            " </script>")
    List<ShareChartDataDto> chartListT(@Param("time") Date time, @Param("storeId") Integer storeId);

    @Select("<script> " +
            " select count(*) from t_share_order " +
            " where is_del=0 and order_status in (1) " +
            " <if test =\"storeId !=null\"> and store_id = #{storeId} </if> " +
            " <if test =\"storeRoomId !=null\"> and store_room_id = #{storeRoomId} </if> " +
            " </script>")
    int findUnServiceOrderCount(@Param("storeId") Integer storeId, @Param("storeRoomId") Integer storeRoomId);

    @Select("<script> " +
            " select UNIX_TIMESTAMP(tea_server_end_time) from t_share_order " +
            " where is_del=0 and (order_status in (2) or (order_status in (4) and 9 > service_status)) " +
            " <if test =\"userId !=null\"> and create_user_id = #{userId} </if> " +
            " <if test =\"storeId !=null\"> and store_id = #{storeId} </if> " +
            " <if test =\"storeRoomId !=null\"> and store_room_id = #{storeRoomId} </if> " +
            " order by tea_server_end_time desc limit 1 </script>")
    Long findUserServerEndTime(@Param("storeId") Integer storeId, @Param("storeRoomId") Integer storeRoomId, @Param("userId") Integer userId);
}
